﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FTBS.Models.Entities;
using FTBS.Models.Entities.Concert;

namespace FTBS.Models.DAO
{
    public class ReportDAO
    {
        private FilmTicketBookingSystemEntities _FTBSEntities = new FilmTicketBookingSystemEntities();

        /// <summary>
        /// Get all suppliers in the system
        /// include deleted or deactivated suppliers
        /// </summary>
        /// <returns></returns>
        public List<string> GetAllSuppliers()
        {
            List<string> suppliers = new List<string>();
            suppliers = (
                from s in _FTBSEntities.Users
                where s.RoleId == 4
                select s.UserName
                ).ToList();
            return suppliers;
        }

        #region  Supplier total report - include bought and sold theater data in each tube
        //====================================================New============================================================
        /// <summary>
        /// Get SOLD and UNSOLD tickets of a supplier
        /// main sorted objects here are theaters of supplier
        /// </summary>
        /// <param name="usn"></param>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns></returns>
        public List<ReportTube> GetSupplierTimeRangeData(string usn, DateTime fTime, DateTime tTime)
        {
            List<ReportTube> timeRangeData = new List<ReportTube>();
            try
            {
                var data = (

                        //from tick in _FTBSEntities.Tickets
                    //from theater in _FTBSEntities.Theaters
                    //from schedule in _FTBSEntities.Schedules
                    //from film in _FTBSEntities.Films
                    //from priceTicket in _FTBSEntities.PriceTickets
                    //from room in _FTBSEntities.Rooms
                    //from chair in _FTBSEntities.Chairs
                    //from chairType in _FTBSEntities.ChairTypes
                    //where
                    //theater.UserName == usn &&
                    //schedule.TheaterId == theater.TheaterId &&
                    //tick.ScheduleId == schedule.ScheduleId &&
                    //film.FilmId == schedule.FilmId &&
                    //tick.IsDeleted == false &&
                    //fTime <= schedule.Date && schedule.Date <= tTime &&
                    //priceTicket.TheaterId == theater.TheaterId 
                        from ticket in _FTBSEntities.Tickets
                        join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                        join theater in _FTBSEntities.Theaters on schedule.TheaterId equals theater.TheaterId
                        join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                        join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                        join priceticket in _FTBSEntities.PriceTickets on theater.TheaterId equals priceticket.TheaterId
                        join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId
                        where
                        ticket.IsDeleted == false &&
                        theater.UserName == usn &&
                        fTime <= schedule.Date && schedule.Date <= tTime &&
                        priceticket.ChairTypeId == chair.ChairTypeId
                        orderby
                        schedule.Date,
                        theater.TheaterName
                        select
                        new
                        {
                            ticketId = ticket.TicketId,
                            username = theater.UserName,
                            supplierName = theater.User.FullName,
                            theaterId = theater.TheaterId,
                            theaterName = theater.TheaterName,
                            date = schedule.Date,
                            time = schedule.ShowTime.Time,
                            isSold = ticket.Status,
                            purePrice = priceticket.Price,
                            soldPrice = ticket.Price
                        }
                    ).ToList();
                if (data.Count > 0)
                {
                    int theater = 0;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in data)
                    {
                        if (!(theater == item.theaterId && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                timeRangeData.Add(reportTube);
                                reportTube = new ReportTube();
                            }

                            theater = item.theaterId;
                            date = item.date;
                            // create new tube
                            reportTube.TheaterId = item.theaterId;
                            reportTube.TheaterName = item.theaterName;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierId = item.username;
                            reportTube.SupplierName = item.supplierName;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = 1;
                                reportTube.TotalPrice = item.soldPrice;
                                reportTube.Balance = item.soldPrice - item.purePrice;
                            }
                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                                reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                                reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            }
                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                    }
                    timeRangeData.Add(reportTube);
                }
            }
            catch (Exception)
            {

                throw;
            }
            return timeRangeData;
        }
        /// <summary>
        /// Get SOLD and UNSOLD tickets of a supplier
        /// main sorted objects here are categories of a supplier
        /// </summary>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns></returns>
        /// 
        public List<ReportTube> GetSupplierCategoriesRank(string usn, DateTime fTime, DateTime tTime)
        {
            List<ReportTube> categoriesRank = new List<ReportTube>();
            try
            {
                var categoriesList = (
                       from ticket in _FTBSEntities.Tickets
                       join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                       join theater in _FTBSEntities.Theaters on schedule.TheaterId equals theater.TheaterId
                       join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                       join category in _FTBSEntities.Categories on film.CategoryId equals category.CategoryId
                       join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                       join priceticket in _FTBSEntities.PriceTickets on theater.TheaterId equals priceticket.TheaterId
                       join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId

                       where
                       ticket.IsDeleted == false &&
                       theater.UserName == usn &&
                       schedule.Date >= fTime && schedule.Date <= tTime &&
                       priceticket.ChairTypeId == chair.ChairTypeId 

                       orderby
                       schedule.Date,
                       category.CategoryId
                       select
                       new
                       {
                           ticketId = ticket.TicketId,
                           category = category.CategoryName,
                           supplierName = theater.User.FullName,
                           username = theater.UserName,
                           theaterName = theater.TheaterName,
                           theaterId = theater.TheaterId,
                           date = schedule.Date,
                           time = schedule.ShowTime.Time,
                           isSold = ticket.Status,
                           purePrice = priceticket.Price,
                           soldPrice = ticket.Price
                       }
                    ).ToList();
                if (categoriesList.Count > 0)
                {
                    string category = null;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in categoriesList)
                    {
                        if (!(category == item.category && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                categoriesRank.Add(reportTube);
                                reportTube = new ReportTube();
                            }

                            category = item.category;
                            date = item.date;
                            // create new tube
                            reportTube.CategoryName = item.category;
                            reportTube.TheaterName = item.theaterName;
                            reportTube.TheaterId = item.theaterId;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierId = item.username;
                            reportTube.SupplierName = item.supplierName;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = 1;
                                reportTube.TotalPrice = item.soldPrice;
                                reportTube.Balance = item.soldPrice - item.purePrice;
                            }
                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                                reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                                reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            }
                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                    }
                    categoriesRank.Add(reportTube);
                }
            }
            catch (Exception)
            {
                throw;
            }

            return categoriesRank;
        }
        #endregion

        #region  Admin total report - include bought and sold system data in each tube
        //=================================================New==============================================
        /// <summary>
        /// Get SOLD and UNSOLD tickets of ALL SUPPLIERS in system
        /// main sorted objects here are Suppliers of system
        /// </summary>
        /// <param name="usn"></param>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns>List report tube of days in a time range</returns>
        public List<ReportTube> GetSystemSuppliersTimeRangeData(DateTime fTime, DateTime tTime)
        {
            List<ReportTube> timeRangeData = new List<ReportTube>();
            try
            {
                var data = (
                        from ticket in _FTBSEntities.Tickets
                        join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                        join theater in _FTBSEntities.Theaters on schedule.TheaterId equals theater.TheaterId
                        join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                        join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                        join priceticket in _FTBSEntities.PriceTickets on theater.TheaterId equals priceticket.TheaterId
                        join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId

                        where
                        ticket.IsDeleted == false &&
                        fTime <= schedule.Date && schedule.Date <= tTime &&
                        priceticket.ChairTypeId == chair.ChairTypeId

                        orderby
                        schedule.Date,
                        theater.UserName
                        select
                        new
                        {
                            supplier = theater.User.FullName,
                            username = theater.UserName,
                            theaterName = theater.TheaterName,
                            theaterId = theater.TheaterId,
                            date = schedule.Date,
                            time = schedule.ShowTime.Time,
                            isSold = ticket.Status,
                            purePrice = priceticket.Price,
                            soldPrice = ticket.Price
                        }
                    ).ToList();
                if (data.Count > 0)
                {
                    string supplier = null;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in data)
                    {
                        if (!(supplier == item.supplier && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                timeRangeData.Add(reportTube);
                                reportTube = new ReportTube();
                            }
                            supplier = item.supplier;
                            date = item.date;
                            // create new tube
                            reportTube.TheaterName = item.theaterName;
                            reportTube.TheaterId = item.theaterId;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierId = item.username;
                            reportTube.SupplierName = item.supplier;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = 1;
                                reportTube.TotalPrice = item.soldPrice;
                                reportTube.Balance = item.soldPrice - item.purePrice;
                            }
                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                                reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                                reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            }
                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                    }
                    timeRangeData.Add(reportTube);
                }
            }
            catch (Exception)
            {

                throw;
            }
            return timeRangeData;
        }
        /// <summary>
        /// Get SOLD and UNSOLD tickets of ALL THEATERS in system
        /// main sorted objects here are THEATERS of system
        /// </summary>
        /// <param name="usn"></param>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns>List report tube of days in a time range</returns>
        public List<ReportTube> GetSystemTheatersTimeRangeData(DateTime fTime, DateTime tTime)
        {
            List<ReportTube> timeRangeData = new List<ReportTube>();
            try
            {
                var data = (  
                        from ticket in _FTBSEntities.Tickets
                        join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                        join theater in _FTBSEntities.Theaters on schedule.TheaterId equals theater.TheaterId
                        join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                        join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                        join priceticket in _FTBSEntities.PriceTickets on theater.TheaterId equals priceticket.TheaterId
                        join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId
                        where
                        ticket.IsDeleted == false &&
                        priceticket.ChairTypeId == chair.ChairTypeId &&
                        fTime <= schedule.Date && schedule.Date <= tTime
                        orderby
                        schedule.Date,
                        theater.TheaterName
                        select
                        new
                        {
                            username = theater.UserName,
                            supplier = theater.User.FullName,
                            theaterId = theater.TheaterId,
                            theaterName = theater.TheaterName,
                            date = schedule.Date,
                            time = schedule.ShowTime.Time,
                            isSold = ticket.Status,
                            purePrice = priceticket.Price,
                            soldPrice = ticket.Price
                        }
                    ).ToList();
                if (data.Count > 0)
                {
                    int theater = 0;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in data)
                    {
                        if (!(theater == item.theaterId && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                timeRangeData.Add(reportTube);
                                reportTube = new ReportTube();
                            }
                            theater = item.theaterId;
                            date = item.date;
                            // create new tube
                            reportTube.TheaterId = item.theaterId;
                            reportTube.TheaterName = item.theaterName;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierName = item.supplier;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = 1;
                                reportTube.TotalPrice = item.soldPrice;
                                reportTube.Balance = item.soldPrice - item.purePrice;
                            }
                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                                reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                                reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            }
                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                    }
                    timeRangeData.Add(reportTube);
                }
            }
            catch (Exception)
            {

                throw;
            }
            return timeRangeData;
        }
        /// <summary>
        /// Get SOLD and UNSOLD tickets of ALL CATEGORIES in system
        /// main sorted objects here are CATEGORIES of system
        /// </summary>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns></returns>
        public List<ReportTube> GetSystemCategoriesRank(DateTime fTime, DateTime tTime)
        {
            List<ReportTube> categoriesRank = new List<ReportTube>();
            try
            {
                var categoriesList = (
                       from ticket in _FTBSEntities.Tickets
                       join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                       join theater in _FTBSEntities.Theaters on schedule.TheaterId equals theater.TheaterId
                       join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                       join category in _FTBSEntities.Categories on film.CategoryId equals category.CategoryId
                       join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                       join priceticket in _FTBSEntities.PriceTickets on theater.TheaterId equals priceticket.TheaterId
                       join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId

                       where
                       ticket.IsDeleted == false &&
                       priceticket.ChairTypeId == chair.ChairTypeId &&
                       schedule.Date >= fTime && schedule.Date <= tTime 

                       orderby
                       schedule.Date,
                       category.CategoryId
                       select
                       new
                       {
                           ticketId = ticket.TicketId,
                           category = category.CategoryName,
                           username = theater.UserName,
                           supplier = theater.User.FullName,
                           theaterName = theater.TheaterName,
                           theaterId = theater.TheaterId,
                           date = schedule.Date,
                           time = schedule.ShowTime.Time,
                           isSold = ticket.Status,
                           purePrice = priceticket.Price,
                           soldPrice = ticket.Price
                       }
                    ).ToList();
                if (categoriesList.Count > 0)
                {
                    string category = null;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in categoriesList)
                    {
                        if (!(category == item.category && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                categoriesRank.Add(reportTube);
                                reportTube = new ReportTube();
                            }

                            category = item.category;
                            date = item.date;
                            // create new tube       
                            reportTube.CategoryName = item.category;
                            reportTube.TheaterId = item.theaterId;
                            reportTube.TheaterName = item.theaterName;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierId = item.username;
                            reportTube.SupplierName = item.supplier;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = 1;
                                reportTube.TotalPrice = item.soldPrice;
                                reportTube.Balance = item.soldPrice - item.purePrice;
                            }

                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                                reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                                reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            }

                            if (item.date <= DateTime.Today)
                            {
                                if (item.date < DateTime.Today)
                                {
                                    reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                }
                                else
                                {
                                    if (item.time.Hours <= DateTime.Today.Hour)
                                    {
                                        if (item.time.Minutes <= DateTime.Today.Minute)
                                        {
                                            reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                                        }
                                    }
                                }
                            }
                            if (add == false) { add = true; }
                        }
                    }
                    categoriesRank.Add(reportTube);
                }
            }
            catch (Exception)
            {
                throw;
            }

            return categoriesRank;
        }

        #endregion

        /// <summary>
        /// Get ONLY SOLD tickets of ONE THEATER in system
        /// main sorted objects here ONE THEATER of system
        /// <param name="theater"></param>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns></returns>
        public List<ReportTube> GetATheaterSoldData(int theater, DateTime fTime, DateTime tTime)
        {
            List<ReportTube> timeRangeData = new List<ReportTube>();
            try
            {
                var data = (
                       from ticket in _FTBSEntities.Tickets
                       join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                       join thea in _FTBSEntities.Theaters on schedule.TheaterId equals thea.TheaterId
                       join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                       join category in _FTBSEntities.Categories on film.CategoryId equals category.CategoryId
                       join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                       join priceticket in _FTBSEntities.PriceTickets on thea.TheaterId equals priceticket.TheaterId
                       join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId
                        where
                        thea.TheaterId == theater &&
                        ticket.IsDeleted == false && ticket.Status == true &&
                        priceticket.ChairTypeId == chair.ChairTypeId &&
                        fTime <= schedule.Date && schedule.Date <= tTime 
                        orderby
                        schedule.Date,
                        thea.TheaterName
                        select
                        new
                        {
                            username = thea.UserName,
                            supplierName = thea.User.FullName,
                            theaterId = thea.TheaterId,
                            theaterName = thea.TheaterName,
                            date = schedule.Date,
                            isSold = ticket.Status,
                            purePrice = priceticket.Price,
                            soldPrice = ticket.Price
                        }
                    ).ToList();
                if (data.Count > 0)
                {
                    int thea = 0;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in data)
                    {
                        if (!(thea == item.theaterId && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                timeRangeData.Add(reportTube);
                                reportTube = new ReportTube();
                            }

                            thea = item.theaterId;
                            date = item.date;
                            // create new tube
                            reportTube.TheaterId = item.theaterId;
                            reportTube.TheaterName = item.theaterName;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierId = item.username;
                            reportTube.SupplierName = item.supplierName;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = 1;
                                reportTube.TotalPrice = item.soldPrice;
                                reportTube.Balance = item.soldPrice - item.purePrice;
                            }
                            if (item.date < DateTime.Today)
                            {
                                reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                            }
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            if (item.isSold == true)
                            {
                                reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                                reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                                reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            }
                            if (item.date < DateTime.Today)
                            {
                                reportTube.NumberOfReturnTicket = reportTube.NumberOfSoldTicket - reportTube.NumberOfTicket;
                            }
                            if (add == false) { add = true; }
                        }
                    }
                    timeRangeData.Add(reportTube);
                }
            }
            catch (Exception)
            {

                throw;
            }
            return timeRangeData;
        }

        #region  Admin total report - include sold data in each tube
        //=================================================New==============================================
        /// <summary>
        /// Get ONLY SOLD tickets of ALL SUPPLIERS in system
        /// main sorted objects here are SUPPLIERS of system
        /// <param name="theater"></param>
        /// <param name="usn"></param>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns>List report tube of days in a time range</returns>
        public List<ReportTube> GetSystemSuppliersTimeRangeSoldData(DateTime fTime, DateTime tTime)
        {
            List<ReportTube> timeRangeData = new List<ReportTube>();
            try
            {
                var data = (
                        from ticket in _FTBSEntities.Tickets
                        join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                        join theater in _FTBSEntities.Theaters on schedule.TheaterId equals theater.TheaterId
                        join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                        join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                        join priceticket in _FTBSEntities.PriceTickets on theater.TheaterId equals priceticket.TheaterId
                        join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId

                        where
                        film.FilmId == schedule.FilmId &&
                        ticket.IsDeleted == false && ticket.Status == true &&
                        fTime <= schedule.Date && schedule.Date <= tTime &&
                        priceticket.ChairTypeId == chair.ChairTypeId 

                        orderby
                        schedule.Date,
                        theater.UserName
                        select
                        new
                        {
                            id = ticket.TicketId,
                            status = ticket.Status,
                            username = theater.UserName,
                            supplier = theater.User.FullName,
                            theaterId = theater.TheaterId,
                            theaterName = theater.TheaterName,
                            date = schedule.Date,
                            purePrice = priceticket.Price,
                            soldPrice = ticket.Price
                        }
                    ).ToList();
                if (data.Count > 0)
                {
                    string supplier = null;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in data)
                    {
                        if (!(supplier == item.supplier && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                timeRangeData.Add(reportTube);
                                reportTube = new ReportTube();
                            }
                            supplier = item.supplier;
                            date = item.date;
                            // create new tube
                            reportTube.TheaterName = item.theaterName;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierId = item.username;
                            reportTube.SupplierName = item.supplier;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            reportTube.NumberOfSoldTicket = 1;
                            reportTube.TotalPrice = item.soldPrice;
                            reportTube.Balance = item.soldPrice - item.purePrice;
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                            reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                            reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            if (add == false) { add = true; }
                        }
                    }
                    timeRangeData.Add(reportTube);
                }
            }
            catch (Exception)
            {

                throw;
            }
            return timeRangeData;
        }
        /// <summary>
        /// Get ONLY SOLD tickets of ALL THEATERS in system
        /// main sorted objects here are THEATERS of system
        /// <param name="theater"></param>
        /// <param name="usn"></param>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns>List report tube of days in a time range</returns>
        public List<ReportTube> GetSystemTheatersTimeRangeSoldData(DateTime fTime, DateTime tTime)
        {
            List<ReportTube> timeRangeData = new List<ReportTube>();
            try
            {
                var data = (
                        from ticket in _FTBSEntities.Tickets
                        join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                        join theater in _FTBSEntities.Theaters on schedule.TheaterId equals theater.TheaterId
                        join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                        join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                        join priceticket in _FTBSEntities.PriceTickets on theater.TheaterId equals priceticket.TheaterId
                        join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId

                        where
                        ticket.IsDeleted == false && ticket.Status == true &&
                        fTime <= schedule.Date && schedule.Date <= tTime &&
                        priceticket.ChairTypeId == chair.ChairTypeId

                        orderby
                        schedule.Date,
                        theater.TheaterName
                        select
                        new
                        {
                            username = theater.UserName,
                            supplier = theater.User.FullName,
                            theaterId = theater.TheaterId,
                            theaterName = theater.TheaterName,
                            date = schedule.Date,
                            purePrice = priceticket.Price,
                            soldPrice = ticket.Price
                        }
                    ).ToList();
                if (data.Count > 0)
                {
                    int theater = 0;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in data)
                    {
                        if (!(theater == item.theaterId && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                timeRangeData.Add(reportTube);
                                reportTube = new ReportTube();
                            }
                            theater = item.theaterId;
                            date = item.date;
                            // create new tube
                            reportTube.TheaterId = item.theaterId;
                            reportTube.TheaterName = item.theaterName;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierId = item.username;
                            reportTube.SupplierName = item.supplier;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            reportTube.NumberOfSoldTicket = 1;
                            reportTube.TotalPrice = item.soldPrice;
                            reportTube.Balance = item.soldPrice - item.purePrice;
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                            reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                            reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            if (add == false) { add = true; }
                        }
                    }
                    timeRangeData.Add(reportTube);
                }
            }
            catch (Exception)
            {

                throw;
            }
            return timeRangeData;
        }
        /// <summary>
        /// Get ONLY SOLD tickets of ALL THEATERS of ONE SUPPLIER in system
        /// main sorted objects here are THEATERS of ONE SUPPLIER
        /// <param name="theater"></param>
        /// <param name="usn"></param>
        /// <param name="fTime"></param>
        /// <param name="tTime"></param>
        /// <returns>List report tube of days in a time range</returns>
        public List<ReportTube> GetSupplierTheatersTimeRangeSoldData(string usn, DateTime fTime, DateTime tTime)
        {
            List<ReportTube> timeRangeData = new List<ReportTube>();
            try
            {
                var data = (
                        from ticket in _FTBSEntities.Tickets
                        join schedule in _FTBSEntities.Schedules on ticket.ScheduleId equals schedule.ScheduleId
                        join theater in _FTBSEntities.Theaters on schedule.TheaterId equals theater.TheaterId
                        join film in _FTBSEntities.Films on schedule.FilmId equals film.FilmId
                        join chair in _FTBSEntities.Chairs on ticket.ChairId equals chair.ChairId
                        join priceticket in _FTBSEntities.PriceTickets on theater.TheaterId equals priceticket.TheaterId
                        join room in _FTBSEntities.Rooms on schedule.RoomId equals room.RoomId

                        where
                        theater.UserName == usn &&
                        ticket.IsDeleted == false && ticket.Status == true &&
                        fTime <= schedule.Date && schedule.Date <= tTime && 
                        priceticket.ChairTypeId == chair.ChairTypeId

                        orderby
                        schedule.Date,
                        theater.TheaterName
                        select
                        new
                        {
                            username = theater.UserName,
                            supplier = theater.User.FullName,
                            theaterId = theater.TheaterId,
                            theaterName = theater.TheaterName,
                            date = schedule.Date,
                            purePrice = priceticket.Price,
                            soldPrice = ticket.Price
                        }
                    ).ToList();
                if (data.Count > 0)
                {
                    int theater = 0;
                    DateTime date = DateTime.MinValue;
                    bool add = false;
                    ReportTube reportTube = new ReportTube();
                    foreach (var item in data)
                    {
                        if (!(theater == item.theaterId && date == item.date))
                        {
                            if (add == true)
                            {
                                // before create new tube, push the old tube to reportData
                                timeRangeData.Add(reportTube);
                                reportTube = new ReportTube();
                            }
                            theater = item.theaterId;
                            date = item.date;
                            // create new tube
                            reportTube.TheaterName = item.theaterName;
                            reportTube.TheaterId = item.theaterId;
                            reportTube.ReportDate = item.date;
                            reportTube.SupplierId = item.username;
                            reportTube.SupplierName = item.supplier;
                            reportTube.NumberOfTicket = 1;
                            reportTube.TotalPurePrice = item.purePrice;
                            reportTube.NumberOfSoldTicket = 1;
                            reportTube.TotalPrice = item.soldPrice;
                            reportTube.Balance = item.soldPrice - item.purePrice;
                            if (add == false) { add = true; }
                        }
                        // else, update old one
                        else
                        {
                            //update number of tiket and totalprice
                            reportTube.NumberOfTicket = reportTube.NumberOfTicket + 1;
                            reportTube.TotalPurePrice = reportTube.TotalPurePrice + item.purePrice;
                            reportTube.NumberOfSoldTicket = reportTube.NumberOfSoldTicket + 1;
                            reportTube.TotalPrice = reportTube.TotalPrice + item.soldPrice;
                            reportTube.Balance = reportTube.Balance + (item.soldPrice - item.purePrice);
                            if (add == false) { add = true; }
                        }
                    }
                    timeRangeData.Add(reportTube);
                }
            }
            catch (Exception)
            {

                throw;
            }
            return timeRangeData;
        }

        #endregion

    }
}